/****** Object:  StoredProcedure [dbo].[GetNewsSummary]    Script Date: 09/06/2013 21:38:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNewsSummary]
	@personId bigint = NULL,
	@maxItems int = 20
AS
BEGIN
	
	DECLARE @news TABLE (
		Id bigint,
		Title nvarchar(255),
		Summary nvarchar(4000),
		IsViewed bit,
		ModificationDate datetime);
		
	INSERT INTO @news (Id, Title, Summary, IsViewed, ModificationDate)
	SELECT
		T.Id,
		T.Title,
		T.Summary,
		0,
		T.ModificationData 
	FROM	
	(SELECT
		*,
		ROW_NUMBER() over (order by ModificationData DESC, CreationDate DESC) as ROW_NUM
	FROM NewsItem) AS T
	WHERE T.ROW_NUM < @maxItems
	
	-- Mark the news that are read
	UPDATE n 
	SET IsViewed = 1
	FROM @news n
		INNER JOIN PersonReadNews prn ON prn.NewsItemId = n.Id
									 AND prn.PersonId = @personId;
	
	-- Mark the news as downloaded
	INSERT INTO PersonNewsDownload (PersonId, NewsItemId, DateDownloaded)
	SELECT 
		@personId,
		n.Id,
		GETDATE()
	FROM @news n
		LEFT JOIN PersonNewsDownload pnd ON pnd.NewsItemId = n.Id
										 AND pnd.PersonId = @personId
	WHERE pnd.Id IS NULL;									 
		
	-- Return the results	 
	SELECT
		Id,
		Title,
		Summary,
		IsViewed,
		ModificationDate
	FROM @news;	
	
END

GO


